DBMS (Part 3)

*41. What is GROUP BY?*

Answer:
GROUP BY is used to group rows having the same values in one or more columns.

It is commonly used with aggregate functions like COUNT(), SUM(), AVG(), MAX(), and MIN().

Example:
Count the number of employees in each department.

━━━━━━━━━━━━━━━━━━━━

*42. What is HAVING?*

Answer:
HAVING is used to filter grouped data after applying the GROUP BY clause.

Difference:
• WHERE → Filters rows before GROUP BY
• HAVING → Filters groups after GROUP BY

━━━━━━━━━━━━━━━━━━━━

*43. Difference Between WHERE and HAVING*

*WHERE*
• Filters individual rows
• Used before GROUP BY
• Cannot use aggregate functions

*HAVING*
• Filters grouped data
• Used after GROUP BY
• Can use aggregate functions

━━━━━━━━━━━━━━━━━━━━

*44. What is ORDER BY?*

Answer:
ORDER BY is used to sort query results.

Types:
• ASC → Ascending Order (Default)
• DESC → Descending Order

━━━━━━━━━━━━━━━━━━━━

*45. Difference Between UNION and UNION ALL*

*UNION*
• Combines results of multiple SELECT queries
• Removes duplicate records

*UNION ALL*
• Combines results of multiple SELECT queries
• Keeps duplicate records
• Faster than UNION

━━━━━━━━━━━━━━━━━━━━

*46. SQL Clauses Order*

Answer:
The general order of SQL clauses is:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

━━━━━━━━━━━━━━━━━━━━

*47. What is a Subquery?*

Answer:
A Subquery is a query written inside another SQL query.

It is also called an Inner Query.

━━━━━━━━━━━━━━━━━━━━

*48. What is a Nested Query?*

Answer:
Nested Query is another name for a Subquery.

━━━━━━━━━━━━━━━━━━━━

*49. What is an Entity?*

Answer:
An Entity is a real-world object that can be identified.

Examples:
• Student
• Employee
• Customer

━━━━━━━━━━━━━━━━━━━━

*50. What is an Attribute?*

Answer:
An Attribute is a property or characteristic of an entity.

Example:

Entity → Student

Attributes:
• Name
• Age
• Roll Number

━━━━━━━━━━━━━━━━━━━━

*51. What is Cardinality?*

Answer:
Cardinality defines the relationship between two database tables.

Types:
• One-to-One (1:1)
• One-to-Many (1:M)
• Many-to-One (M:1)
• Many-to-Many (M:N)

Examples:
• One Student → One ID Card (1:1)
• One Department → Many Students (1:M)
• Many Employees → One Department (M:1)
• Many Students : Many Courses (M:N)

━━━━━━━━━━━━━━━━━━━━

*52. What is an ER Diagram?*

Answer:
ER (Entity Relationship) Diagram is a graphical representation of a database structure.

It contains:
• Entities
• Attributes
• Relationships
• Primary Keys
• Foreign Keys

━━━━━━━━━━━━━━━━━━━━

*53. SQL Execution Order*

Answer:
Although we write SQL differently, SQL executes in this order:

FROM
↓

WHERE
↓

GROUP BY
↓

HAVING
↓

SELECT
↓

ORDER BY

━━━━━━━━━━━━━━━━━━━━

*54. Common SQL Interview Queries*

Frequently Asked SQL Questions:

• Find Highest Salary
• Find Second Highest Salary
• Find Nth Highest Salary
• Find Duplicate Records
• Delete Duplicate Records
• Count Employees
• Employees Department-wise
• Maximum Salary Department-wise
• Average Salary
• Students Above Average Marks

━━━━━━━━━━━━━━━━━━━━

*55. Most Asked Rapid Fire Questions*

✔ What is DBMS?
✔ Difference between DBMS & RDBMS
✔ What is SQL?
✔ Types of SQL Commands
✔ Primary Key
✔ Foreign Key
✔ Candidate Key
✔ Composite Key
✔ Constraints
✔ Normalization
✔ 1NF
✔ 2NF
✔ 3NF
✔ BCNF
✔ Joins
✔ DELETE vs TRUNCATE vs DROP
✔ ACID Properties
✔ Index
✔ View
✔ Trigger
✔ Stored Procedure
✔ Transaction
✔ COMMIT
✔ ROLLBACK
✔ Aggregate Functions
✔ GROUP BY
✔ HAVING
✔ UNION vs UNION ALL
✔ WHERE vs HAVING
✔ ER Diagram
✔ Cardinality
✔ Data Integrity
✔ Referential Integrity
✔ NULL
✔ Deadlock
✔ Concurrency
✔ Locking
✔ SQL Execution Order

━━━━━━━━━━━━━━━━━━━━

*HR / Technical Question*

Q. Which database have you worked with?

Answer:

"I have primarily worked with MySQL using phpMyAdmin for my academic and personal projects. I used it to create tables, define relationships using Primary and Foreign Keys, perform CRUD operations, write SQL queries, and apply concepts such as Joins, Normalization, Constraints, Aggregate Functions, and Transactions. Through these projects, I gained a solid understanding of relational database design and SQL fundamentals."

━━━━━━━━━━━━━━━━━━━━

*30-Second Quick Revision*

• GROUP BY → Groups Similar Records
• HAVING → Filters Grouped Data
• WHERE → Filters Rows
• ORDER BY → Sorts Data
• UNION → Removes Duplicates
• UNION ALL → Keeps Duplicates
• Subquery → Query Inside Another Query
• Entity → Real-world Object
• Attribute → Property of Entity
• Cardinality → Relationship Between Tables
• ER Diagram → Database Blueprint
• SQL Execution → FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
• Index → Faster Searching
• View → Virtual Table
• Trigger → Automatic Execution
• Transaction → Group of SQL Operations
• ACID → Atomicity, Consistency, Isolation, Durability
• Data Integrity → Accurate & Consistent Data
• Referential Integrity → Valid Foreign Key Relationship